The present invention relates, in general, to database management systems and, in particular, to a method and system for more efficiently processing a bill of material data file stored in a relational database when performing a multilevel explosion of the data file.
For several engineering and production planning application areas, bill of material processing is a key application. The bill of material application is the basis for production and inventory control, cost accounting, and the implementation of engineering changes. Bills of material are structured so that components of each part are provided recursively, which allows the subcomponents of a part or the use of a part within higher level assemblies to be provided quickly. The relationship among parts in a bill of material is many-to-many. A part can require a number of other parts and can also be used in higher level parts. The relationship among parts can be complex, but it cannot be cyclical.
Implementing a bill of material in a relational database system is straightforward but presents performance problems during a parts explosion due to limitations contained in corresponding database languages. A commercially available relational database management system is IBM Corporation's Database 2 (DB2) which uses the Structured Query Language (SQL) as the high level data access language. One approach to storing bills of material in a DB2 database involves the use of two tables, one to define all data relating to an item (master item table), and a second one which defines single level bills of material (bill of material component table). This approach allows the database user to determine the immediate subcomponents of an assembly (single level parts explosion) or to determine in which assemblies a subcomponent is used (single level parts implosion). In embedded SQL, the SELECT statement retrieves an entire set of records, i.e., a table. The mechanism used to access records in the set one by one is referred to as a cursor which is essentially a pointer providing addressability to each of the records in turn. OPEN, FETCH, and CLOSE are three executable statements provided by SQL to operate on cursors. The OPEN statement activates a cursor and executes the query associated with the cursor, thereby identifying a current active set of records. The FETCH statement advances the cursor to the next record in the active set. The CLOSE statement deactivates the cursor. An excellent discussion of relational database systems is contained in "An Introduction to Database Systems", Vol. I, 4th Ed., by C. J. Date, Addison-Wesley Publishing Company, Inc., 1986.
Current algorithms for performing multilevel bill of material explosions on a relational database require the use of multiple open cursors to perform the depth first search for components. This results in two specific problems. The first of these problems arises since cursors are declared statically with a different cursor required for each new level in the bill of material; the limit on the maximum number of levels that may exist in any bill of material must be set to the number of cursors declared in the explosion routine. The second problem is the inaccessibility of large parts of the database during execution of the explosion routine since each open cursor locks the database row that it has fetched.
The present invention solves both of these problems by using control data structures during bill of material explosions, which remove the need for multiple open cursors and also reduce the time during which cursors remain open.